/*
@author: Edgar Pereira
Nº 6207
*/

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <Python.h>
#include <string.h>
#include <locale.h>
#include "myDataProcessing.h"


//#############################################################################
//qsort comparison functions
//#############################################################################

// qsort struct nTeachersYear by integer comparison function
int strucCompare(struct nTeachersYear *a, struct nTeachersYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucCompare

// qsort struct nTeachersEstabYear by integer comparison function
int strucnTeachersEstabYearCompare(struct nTeachersEstabYear *a, struct nTeachersEstabYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersEstabYearCompare

// qsort struct nTeachersDegreeYear by integer comparison function
int strucnTeachersDegreeYearCompare(struct nTeachersDegreeYear *a, struct nTeachersDegreeYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersDegreeYearCompare


// qsort struct lHolDeg by string comparison function
int strucnHolDegYear(struct lHolDeg *a, struct lHolDeg *b){ 
	struct lHolDeg *ia = (struct lHolDeg *)a;
    struct lHolDeg *ib = (struct lHolDeg *)b;
    return strcmp(ia->name, ib->name);
} // end of strucnTeachersDegreeYearCompare

// qsort struct nTeachersDegreeEstYear by integer comparison function
int strucnTeachersDegreEestYearCompare(struct nTeachersDegreeEstYear *a, struct nTeachersDegreeEstYear *b){ 
	if ( a->number < b->number){
		return -1;
	}
	else if (a->number > b->number){
		return 1;
	}
	else {
		return 0;
	}
} // end of strucnTeachersDegreeYearCompare


// qsort struct strucnLHolDeg by string comparison function
int strucnLHolDeg(struct lHolDeg *a, struct lHolDeg *b){ 
	struct lHolDeg *ia = (struct lHolDeg *)a;
    struct lHolDeg *ib = (struct lHolDeg *)b;
    return strcmp(ia->degree, ib->degree);
} // end of strucnTeachersDegreeYearCompare

// qsort struct lEstabYear by string comparison function
int strucnEstabYear(struct lEstabYear *a, struct lEstabYear *b) 
{ 
    struct lEstabYear *ia = (struct lEstabYear *)a;
    struct lEstabYear *ib = (struct lEstabYear *)b;
    return strcmp(ia->name, ib->name);
} // end of strucnStringCmp

// qsort int comparison function
int int_cmp(const void *a, const void *b) { 
    const int *ia = (const int *)a; // casting pointer types 
    const int *ib = (const int *)b;
    return *ia  - *ib; 
	// integer comparison: returns negative if b > a 
	// and positive if a > b 
} // end of int_cmp

//#############################################################################



//#############################################################################
//sqlite.exec callback functions
//#############################################################################

//callback to void int construction
int callback(void **contagem, int argc, char **argv, char **azColName){
	*contagem = atoi( argv[0] );
	
	return 0;
} // end callback

//callback to the nTeachersEstabYear structure construction
int callbackTuple(struct nTeachersEstabYear **nTeachersEstabYear, int argc, char **argv, char **azColName){

	(*nTeachersEstabYear)->estab = malloc((strlen(argv[0])+1)*sizeof(char));

	strcpy((*nTeachersEstabYear)->estab, argv[0]);
	(*nTeachersEstabYear)->number = atoi(argv[1]);
	(*nTeachersEstabYear)++;

    return 0;
} // end callbackTuple

//callback to the callbackHolDeg structure construction
int callbackHolDeg(struct lHolDeg ** lHolDeg, int argc, char **argv, char **azColName){
	//alocation of memory
	(*lHolDeg)->name = malloc((strlen(argv[0])+1)*sizeof(char));
	(*lHolDeg)->degree = malloc((strlen(argv[1])+1)*sizeof(char));

	strcpy((*lHolDeg)->name, argv[0]);
	strcpy((*lHolDeg)->degree, argv[1]);
	(*lHolDeg)++;

    return 0;
} // end callbackHolDeg

//callback to the nTeachersDegreeEstYear structure construction
int callbacknTeachersDegreeEstYear(struct nTeachersDegreeEstYear **nTeachersDegreeEstYear, int argc, char **argv, char **azColName){
	//alocation of memory
	(*nTeachersDegreeEstYear)->degree = malloc((strlen(argv[0])+1)*sizeof(char));
	(*nTeachersDegreeEstYear)->estab = malloc((strlen(argv[1])+1)*sizeof(char));

	strcpy((*nTeachersDegreeEstYear)->degree, argv[0]);
	strcpy((*nTeachersDegreeEstYear)->estab, argv[1]);
	(*nTeachersDegreeEstYear)->number = atoi(argv[2]);
	(*nTeachersDegreeEstYear)++;

    return 0;
} // end callbacknTeachersDegreeEstYear

//callback to the elEstabYear structure construction
int callbackEstList(struct lEstabYear **lEstabYear, int argc, char **argv, char **azColName){
	//alocation of memory
	(*lEstabYear)->name = malloc((strlen(argv[0])+1)*sizeof(char));

	strcpy((*lEstabYear)->name, argv[0]);
	(*lEstabYear)++;

    return 0;
} // end callbackEstList

//#############################################################################



//#############################################################################
//qsort function made from a web example to bether understand the architecture
//#############################################################################
void mYqSort(void* base[], int nel, int width, compfn *function){
// from http://www.anyexample.com/programming/c/qsort__sorting_array_of_strings__integers_and_structs.xml
	
	qsort((void *) base,	// base — is a pointer to the beginning of data array
	nel,					// nel — is a number of elements
	width,					// width — is a size of each element (in bytes)
	(compfn)function);		// compar — is a callback function (pointer to function), which does comparison and returns positive or negative integer depending on //result.

} // end mYqSort



//#############################################################################
// function returning  the number of establishments per each year
// used to calculate the others statistics
//#############################################################################
PyObject* nEstabYear(){
	//printf("nEstabYear\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	PyObject* theListEst = PyList_New(0);
	struct nEstabYear resultEst[10];
	int *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200";
        strcat(string, PyString_AsString(PyString_FromFormat("%d",i)));
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(DISTINCT estabelecimento) FROM fichas_docencia ORDER BY estabelecimento";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		struct nEstabYear resultEstAlias;
		resultEst[i].year = i;
		resultEst[i].number = contagem;	
	}
	
	for(i = 0; i < 10; i++){
		PyObject* tEstYear = PyTuple_New(2);
		PyTuple_SetItem( tEstYear, 0, PyInt_FromLong(resultEst[i].year));
		PyTuple_SetItem( tEstYear, 1, PyInt_FromLong(resultEst[i].number));
		PyList_Append(theListEst, tEstYear);
	}
	return theListEst;		
	
} // end nEstabYear



//#############################################################################
// function returning  the number of degrees per each year
// used to calculate the others statistics
//#############################################################################
PyObject* nDegreeYear(){
	//printf("nDegreeYear\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	PyObject* theListDeg = PyList_New(0);
	struct nDegreeYear resultDeg[10];
	PyObject *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200";
        strcat(string, PyString_AsString(PyString_FromFormat("%d",i)));
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(DISTINCT grau) FROM fichas_docencia ORDER BY grau";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		resultDeg[i].year = i;
		resultDeg[i].number = contagem;	
	}

		for(i = 0; i < 10; i++){
		PyObject* tDegreeYear = PyTuple_New(2);
		PyTuple_SetItem( tDegreeYear, 0, PyInt_FromLong(resultDeg[i].year));
		PyTuple_SetItem( tDegreeYear, 1, PyInt_FromLong(resultDeg[i].number));
		PyList_Append(theListDeg, tDegreeYear);
	}
	
	return theListDeg;		
	
} // end nDegreeYear



//#############################################################################
// function returning  the number of establishments and degree
// used to calculate the others statistics
//#############################################################################
int* nEstabDeg(){
	//printf("nEstabDeg\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	int contagem;

	int* theDegEstList = malloc(10*sizeof(int));
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200";
        strcat(string, PyString_AsString(PyString_FromFormat("%d",i)));
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT sum(estabelecimentos) FROM (SELECT count(Distinct estabelecimento)\
						 AS estabelecimentos FROM fichas_docencia GROUP by grau ORDER by estabelecimento)";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		theDegEstList[i] = contagem;
		printf("theDegEstList[%d] = %d\n", i, theDegEstList[i]);
	}
	
	return theDegEstList;		
	
} // end nDegreeYear


//#############################################################################
// function returning  the number of teachers per year
// used to calculate the others statistics
//#############################################################################
PyObject* nTeachYear(){
	//printf("nTeachYear()\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	PyObject* theList = PyList_New(0);
	struct nTeachersYear result[10];
	
	int *contagem;
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200";
        strcat(string, PyString_AsString(PyString_FromFormat("%d",i)));
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "SELECT COUNT(*) FROM docentes";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }

		sqlite3_close(db);

		result[i].year = i;
		result[i].number = contagem;
		
		
	}

	mYqSort(result, 10, sizeof(struct nTeachersYear), strucCompare);

	for(i = 0; i < 10; i++){
		PyObject* tYear = PyTuple_New(2);
		PyTuple_SetItem( tYear, 0, PyInt_FromLong(result[i].year));
		PyTuple_SetItem( tYear, 1, PyInt_FromLong(result[i].number));
		PyList_Append(theList, tYear);
	}		

	return theList;

	
} // end teachers_Year



//#############################################################################
// function returning  the number of teachers and degree
// used to calculate the others statistics
//#############################################################################
int* nTeachDeg(){
	//printf("nEstabDeg\n\n");

	int i;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	int contagem;

	int* theTeachEstList = malloc(10*sizeof(int));
	
	for (i = 0; i <  10; i++){

		char string[25] = "rebidesDB/rebides200";
        strcat(string, PyString_AsString(PyString_FromFormat("%d",i)));
        strcat (string, ".db");
        char* dbName = (string);
        char* sqlInst = "Select COUNT (*) as total FROM (SELECT docente, grau  FROM fichas_docencia\
							GROUP BY docente, grau\
							ORDER BY docente, grau)";

        rc = sqlite3_open(dbName, &db);

        rc = sqlite3_exec(db, sqlInst, callback, &contagem, &zErrMsg);
        if( rc!=SQLITE_OK ){
                fprintf(stderr, "SQL error: %s\n", zErrMsg);
                sqlite3_free(zErrMsg);
        }
		sqlite3_close(db);
		
		theTeachEstList[i] = contagem;
		printf("theTeachEstList[%d] = %d\n", i, theTeachEstList[i]);
	}
	
	return theTeachEstList;		
	
} // end nTeachDeg



//#############################################################################
// function returning teh number of teachers per establishment
// used to calculate the others statistics
//#############################################################################
struct nTeachersEstabYear* nTeachEstYear(int year, int nEstab){

	int i = 0;
	int j = 0;
	int rc = 0;	
	int nEst = 0;
	int sizeoftrings = 0;
	sqlite3 *db;

	char *zErrMsg = 0;
	char* sqlInst;

	//PyObject* theListTeachEst = PyList_New(0);
	PyObject* ListnEst = PyList_New(10);

	struct nTeachersEstabYear* resultTEY;
	struct nTeachersEstabYear* resultTEYAlias;


	ListnEst = nEstabYear();

	/*// to calculate the total nº of institutions to allocate memory
	nEst = PyInt_AS_LONG(PyTuple_GetItem(PyList_GET_ITEM(ListnEst, 
	                                                     PyInt_AsSsize_t(PyInt_FromLong(year))),PyInt_AsSsize_t(PyInt_FromLong(1))));*/
	/*//malloc with the entire list
	resultTEY = malloc(nEst*sizeof(struct nTeachersEstabYear));*/
	resultTEY = malloc(nEstab*sizeof(struct nTeachersEstabYear)); // allocates only for nEstab institutions

	resultTEYAlias = resultTEY;

	char string[25] = "rebidesDB/rebides200";
	strcat(string, PyString_AsString(PyString_FromFormat("%d",year)));
	strcat (string, ".db");
	char* dbName = (string);

	/*// to return the entire list
	char* sqlInst = "SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente)\
						AS number FROM fichas_docencia GROUP BY estabelecimento ORDER by number";*/
	// returns only the nEstab first
	char first[] = "SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente)\
						AS number FROM fichas_docencia GROUP BY estabelecimento ORDER by number DESC LIMIT ";
	char* second = malloc(strlen(PyString_AsString(PyString_FromFormat("%d", nEstab)))*sizeof(char));
	
	strcpy(second, PyString_AsString(PyString_FromFormat("%d", nEstab)));
		
	
	sqlInst = malloc((strlen(first)+strlen(second)+1)*sizeof(char));
	strcpy (sqlInst, first);
	strcat(sqlInst, second);
		

	rc = sqlite3_open(dbName, &db);

	rc = sqlite3_exec(db, sqlInst, callbackTuple, &resultTEY, &zErrMsg);
	if( rc!=SQLITE_OK ){
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}

	return resultTEYAlias;		

} // end nTeachEstYear